﻿Imports Global_Visas_Business_Layer
Imports System.ComponentModel
Imports Word
Imports Telerik.Windows.Controls
Imports Telerik.Windows.Data
Imports System.Data
Imports System.Text
Partial Public Class Window
    Dim frm_MenuLayout As Integer
    Dim objDTGrid As DataTable

    Dim StrFieldNames() As String

    Public Enum CaseStatus
        PreSale = 1
        Initiated = 2
        All = 3
    End Enum

    Public Enum ConRole
        SalesCon = 1
        CaseWorker = 2
        Either = 3
        CaseCoordinator = 4
    End Enum

    Public Enum OnHold
        Exclude = 1
        Include = 2
        Only = 3
    End Enum

    Private ReadOnly Property StatusOnHold() As OnHold
        Get
            If Me.radHoldExclude.IsChecked Then
                Return OnHold.Exclude
            ElseIf Me.radHoldInclude.IsChecked Then
                Return OnHold.Include
            ElseIf Me.radHoldOnly.IsChecked Then
                Return OnHold.Only
            End If
        End Get
    End Property

    Private ReadOnly Property StatusCaseStatus() As CaseStatus
        Get
            If Me.radViewPreSale.IsChecked Then
                Return CaseStatus.PreSale
            ElseIf Me.radViewInitiated.IsChecked Then
                Return CaseStatus.Initiated
            ElseIf Me.radViewAll.IsChecked Then
                Return CaseStatus.All
            End If
        End Get
    End Property

    Private ReadOnly Property StatusConRole() As ConRole
        Get
            If Me.radRoleCW.IsChecked Then
                Return ConRole.CaseWorker
            ElseIf Me.radRoleEither.IsChecked Then
                Return ConRole.Either
            ElseIf Me.radRoleSales.IsChecked Then
                Return ConRole.SalesCon
            ElseIf Me.radRoleCaseCoordinator.IsChecked Then
                Return ConRole.CaseCoordinator
            End If
        End Get
    End Property

    Private Sub btnGoTo_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles btnGoTo.Click
        Try
            Me.DoSearch()
        Catch ex As Exception
        End Try

    End Sub

    Private Sub DoSearch()

        Try

            Dim lngSearchID As Long
            Dim strSearchResult As String
            Dim strName As String = ""
            If Not IsNumeric(Me.txtSearch.Text) Then

                If radSearchCase.IsChecked Then strName = radSearchCase.Content.ToString
                If radSearchCompany.IsChecked Then strName = radSearchCompany.Content.ToString
                If radSearchPerson.IsChecked Then strName = radSearchPerson.Content.ToString
                If radSearchInvoice.IsChecked Then strName = radSearchInvoice.Content.ToString
                MsgBox("Please enter ID of the " & strName & ".", MsgBoxStyle.Information, "Search")
                Exit Sub
            Else
                lngSearchID = Me.txtSearch.Text
            End If

            If Me.radSearchCase.IsChecked Then
                ' Case search
                GlobalCRM.CurrentSearchType = GlobalCRM.enuSearchType.eCase
            ElseIf Me.radSearchCompany.IsChecked Then
                ' Company search
                GlobalCRM.CurrentSearchType = GlobalCRM.enuSearchType.eCompany
            ElseIf Me.radSearchPerson.IsChecked Then
                ' Person search
                GlobalCRM.CurrentSearchType = GlobalCRM.enuSearchType.ePerson
            ElseIf Me.radSearchInvoice.IsChecked Then
                ' Invoice search
                GlobalCRM.CurrentSearchType = GlobalCRM.enuSearchType.eInvoice
            End If

            ' run the search
            strSearchResult = CommonDataFunctions.IDSearch(lngSearchID, GlobalCRM.CurrentSearchType)
            If strSearchResult = "ok" Then
                'Load frmMaindisplay
                Dim mainDisplayForms As New frmMainDisplay()
                mainDisplayForms.ShowDialog()
            Else
                MsgBox(strSearchResult, , "Go To")
            End If

        Catch ex As Exception
        End Try
    End Sub

    Private Sub gvlogo_ImageFailed(ByVal sender As System.Object, ByVal e As System.Windows.ExceptionRoutedEventArgs)
        Dim dlg As Microsoft.Win32.OpenFileDialog = New Microsoft.Win32.OpenFileDialog()
        Dim result As Boolean = dlg.ShowDialog()
        If result = True Then
            'gvlogo.Source = New BitmapImage(New Uri(dlg.FileName))
        Else
            'gvlogo.Source = New BitmapImage(New Uri("\\constellation\e\it\Deployment\GVLogo\visa_logo.jpg"))
        End If
    End Sub

    Private Sub btnConname_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs)
        Try
            Me.drpBranch.SelectedValue = CommonDataFunctions.GetLoggedOnConsultantBranchID()
            Me.drpDepts.Text = CommonDataFunctions.GetDepartmentName
            Me.drpCons.SelectedValue = GlobalCRM.CurrentConID

        Catch ex As Exception

        End Try

    End Sub

    Private Sub MDIForm_Loaded(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles Me.Loaded

        Try

            Dim isActive As Long = 1
            Me.lblUser.Content = CommonDataFunctions.GetConsultantNameNT
            If Me.lblUser.Content = String.Empty Then
                MsgBox("The user has not been set up in the system. Please contact the administrator.", MsgBoxStyle.Critical, "User Not Set up")
                End
            End If
            isActive = CommonDataFunctions.GetConsultantStatus(Environment.UserName.Trim)

            If isActive = 0 Then
                MsgBox("The user is inactive in the system. Please activate the CRM and try again. " & vbCrLf & "Contact your manager for more details.")
                End
            End If
            Dim _ConBranchID As Integer = CommonDataFunctions.GetLoggedOnConsultantBranchID
            Dim _ConDeptID As Integer = CommonDataFunctions.GetLoggedOnConsultantDeptID(Environment.UserName.Trim)


            'Me.btnConname.Content = Me.lblUser.Content
            With Me.drpBranch
                .SelectedValuePath = "Branch_ID"
                .DisplayMemberPath = "Branch_Name"
                .ItemsSource = CType(CommonDataFunctions.BranchList, IListSource).GetList
                .SelectedValue = _ConBranchID
            End With
            AddHandler drpBranch.SelectionChanged, AddressOf drpBranch_SelectionChanged
            With Me.drpDepts
                .SelectedValuePath = "Department_ID"
                .DisplayMemberPath = "Department_Name"
                .ItemsSource = CType(CommonDataFunctions.DepartmentList(Me.drpBranch.SelectedValue), IListSource).GetList
                .SelectedValue = _ConDeptID
            End With
            AddHandler drpDepts.SelectionChanged, AddressOf drpDepts_SelectionChanged
            RemoveHandler drpCons.SelectionChanged, AddressOf drpCons_SelectionChanged
            With Me.drpCons
                .SelectedValuePath = "con_id"
                .DisplayMemberPath = "con_name"
                .ItemsSource = CType(CommonDataFunctions.ConsultantList(True, drpDepts.SelectedValue), IListSource).GetList
            End With
            AddHandler drpCons.SelectionChanged, AddressOf drpCons_SelectionChanged

            RemoveHandler drpTaskView.SelectionChanged, AddressOf drpTaskView_SelectionChanged
            Me.drpTaskView.SelectedIndex = 0
            AddHandler drpTaskView.SelectionChanged, AddressOf drpTaskView_SelectionChanged

            Me.drpTaskView.SelectedIndex = 0
            Me.drpView.SelectedIndex = 0
            Me.drpDepts.Text = CommonDataFunctions.GetDepartmentName
            Me.drpCons.Text = Me.lblUser.Content
            GlobalCRM.CurrentConID = Me.drpCons.SelectedValue

            drpCons_SelectionChanged(Nothing, New System.EventArgs)


            If CommonDataFunctions.GetDBConnectionString.ToLower.IndexOf("constellation") > 0 Then
                Me.Background = New SolidColorBrush((Colors.Cyan))
            End If

            If drpDepts.Items.Count = 0 Then
                drpCons.ItemsSource = Nothing
                drpCons.Items.Clear()
            End If
            If CommonDataFunctions.LogInUser(GlobalCRM.CurrentConID) = True Then

            End If
            'check whether the user has latest version
            'Dim strVersion As String = CommonFunc.LatestVersionRolledOut.Trim
            'If strVersion <> Application.ProductVersion.Trim Then
            '    If MsgBox("Latest version of CRM v" & strVersion & " is available now. " & vbCrLf & "If you would like to install the latest version, please push the button 'Yes'." & vbCrLf & vbCrLf & "All the other programs remain unaffected.", MsgBoxStyle.YesNo, "Latest Version") = MsgBoxResult.Yes Then
            '        'frmMDIMain.InstallLatstVersionToolStripMenuItem_Click(sender, e)
            '        System.Diagnostics.Process.Start("C:\Database\CRMInstallation.Bat")
            '        End
            '        'btnExit_Click(sender, e)
            '    Else
            '    End If
            'End If
            'Me.RefreshLists()
        Catch ex As Exception
        End Try
    End Sub

    'Public Sub UpdateView(ByVal Sender As Object, ByVal e As System.EventArgs)
    '    Try
    '        objDTGrid = DB.GetSQLDataTable(Me.CaseListSQL(Me.StatusCaseStatus, Me.StatusConRole, OnHold.Exclude))
    '        Me.grdCases.DataSource = objDTGrid
    '        Me.GetColumns()
    '    Catch ex As Exception
    '        CommonFunc.ErrorHandler(ex, "frmMainForm.UpdateView", False)
    '    End Try

    'End Sub
    'Private Sub drpCons_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles drpCons.SelectedIndexChanged
    '    ' respond to Consultant selection in drop down list
    '    Try
    '        Me.Cursor = Cursors.Wait

    '        ' populate Task listview for current consultant
    '        DataBinding.BindListView(EventList(Me.drpCons.SelectedValue, Me.TaskViewEnd), Me.lvwEventsOutstanding)

    '        ' populate Cases datagrid for selected consultant
    '        objDTGrid = DB.GetSQLDataTable(Me.CaseListSQL(Me.StatusCaseStatus, Me.StatusConRole, OnHold.Exclude))
    '        ' Automate the task for cases more than 90 days old
    '        If sender Is Nothing And Not objDTGrid Is Nothing Then
    '            If objDTGrid.Rows.Count > 0 Then
    '                Dim i As Integer
    '                For i = 0 To objDTGrid.Rows.Count - 1
    '                    Dim sql As New StringBuilder
    '                    sql.Append("select case_statusid,case_dtstatus from D_case where case_id = " & objDTGrid.Rows(i).Item("Col_1"))
    '                    Dim dt As DataTable = DB.GetSQLDataTable(sql.ToString)
    '                    If Not dt Is Nothing Then
    '                        If dt.Rows.Count > 0 Then
    '                            If dt.Rows(0).Item("case_statusid") >= 100 And dt.Rows(0).Item("case_dtstatus").ToString <> "" Then
    '                                Dim dtToCheck As Date = dt.Rows(0).Item("case_dtstatus")
    '                                If System.DateTime.Today > dtToCheck.AddDays(GlobalCRM.DaysOld) Then
    '                                    'add an event
    '                                    TasksEvents.GblFunc_Add_Event("Collect Payment from Client ", "The case is over " & GlobalCRM.DaysOld & " days old." & vbCrLf & "Please chase the client for Payment", TasksEvents.EventStatus.Task, objDTGrid.Rows(i).Item("Col_1"), tasksevents.EventTypes.EventCase, 0, 0, GlobalCRM.CurrentConID, Format(Now, "dd-MMM-yyyy"), 1)
    '                                End If
    '                            End If
    '                        End If
    '                    End If
    '                Next
    '            End If
    '        End If
    '        Me.grdCases.DataSource = objDTGrid
    '        Me.GetColumns()

    '        ' set global variable for current consultant you are viewing info for
    '        GlobalCRM.CurrentConViewID = Me.drpCons.SelectedValue

    '        Me.Cursor = Cursors.Default
    '    Catch ex As Exception
    '        CommonFunc.ErrorHandler(ex, "frmMainForm.drpCons.SelectedIndexChanged", False)
    '    End Try

    'End Sub

    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles btnExit.Click
        Try
            Me.Close()
        Catch ex As Exception
            CommonFuncs.ErrorHandler(ex, "frmMainForm.btnExit_Click", False)
        End Try
    End Sub

    Private Sub drpDepts_SelectionChanged(ByVal sender As Object, ByVal e As System.Windows.Controls.SelectionChangedEventArgs)
        Try
            RemoveHandler drpCons.SelectionChanged, AddressOf drpCons_SelectionChanged

            With Me.drpCons
                .ItemsSource = Nothing
                .Items.Clear()
                .SelectedValuePath = "con_id"
                .DisplayMemberPath = "con_name"
                .ItemsSource = CType(CommonDataFunctions.ConsultantList(True, Me.drpDepts.SelectedValue, , Me.drpBranch.SelectedValue), IListSource).GetList
                .SelectedIndex = -1
            End With

            If drpDepts.Items.Count = 0 Then
                drpCons.ItemsSource = Nothing
                drpCons.Items.Clear()
            End If

            AddHandler drpCons.SelectionChanged, AddressOf drpCons_SelectionChanged
        Catch ex As Exception
        End Try
    End Sub

    Private Function TaskViewEnd() As Date
        ' convert TaskView selection into a date
        Dim selectedDate As String = String.Empty
        selectedDate = CType((drpTaskView.SelectedItem), ComboBoxItem).Content
        Try
            If selectedDate.ToLower.Contains("today") Or selectedDate = String.Empty Then
                Return Format(Now, "dd-MMM-yyyy") & " 23:59:59"
            ElseIf selectedDate.ToLower.Contains("tomorrow") Then
                Return Format(Now.AddDays(1), "dd-MMM-yyyy") & " 23:59:59"
            ElseIf selectedDate.ToLower.Contains("week") Then
                Return Format(Now.AddDays(7), "dd-MMM-yyyy") & " 23:59:59"
            ElseIf selectedDate.ToLower.Contains("month") Then
                Return Format(Now.AddMonths(1), "dd-MMM-yyyy") & " 23:59:59"
            ElseIf selectedDate.ToLower.Contains("all") Then
                Return Format(Now.AddYears(10), "dd-MMM-yyyy") & " 23:59:59"
            End If
        Catch ex As Exception
            CommonFuncs.ErrorHandler(ex, "frmMainForm.TaskViewEnd", False)
        End Try

    End Function

    Private Sub drpCons_SelectionChanged(ByVal sender As Object, ByVal e As System.Windows.Controls.SelectionChangedEventArgs)
        ' respond to Consultant selection in drop down list

        Try
            'Me.Cursor = Cursors.WaitCursor

            ' populate Task listview for current consultant
            Dim itemTable As DataTable
            itemTable = CommonDataFunctions.GetSQLDatatable(EventList(Me.drpCons.SelectedValue, Me.TaskViewEnd))
            DataBinding.BindListView(itemTable, Me.lvwEventsOutstanding)

            ' populate Cases datagrid for selected consultant
            objDTGrid = CommonDataFunctions.GetSQLDataTable(Me.CaseListSQL(Me.StatusCaseStatus, Me.StatusConRole, OnHold.Exclude))
            ' Automate the task for cases more than 90 days old
            If sender Is Nothing And Not objDTGrid Is Nothing Then
                If objDTGrid.Rows.Count > 0 Then
                    Dim i As Integer
                    For i = 0 To objDTGrid.Rows.Count - 1
                        Dim sql As New StringBuilder
                        sql.Append("select case_statusid,case_dtstatus from D_case where case_id = " & objDTGrid.Rows(i).Item("Col_1"))
                        Dim dt As DataTable = CommonDataFunctions.GetSQLDatatable(sql.ToString)
                        If Not dt Is Nothing Then
                            If dt.Rows.Count > 0 Then
                                If dt.Rows(0).Item("case_statusid") >= 100 And dt.Rows(0).Item("case_dtstatus").ToString <> "" Then
                                    Dim dtToCheck As Date = dt.Rows(0).Item("case_dtstatus")
                                    If System.DateTime.Today > dtToCheck.AddDays(GlobalCRM.DaysOld) Then
                                        'add an event
                                        Dim events As New Global_Visas_Business_Layer.TasksEvents()
                                        events.GblFunc_Add_Event("Collect Payment from Client ", "The case is over " & GlobalCRM.DaysOld & " days old." & vbCrLf & "Please chase the client for Payment", TasksEvents.EventStatus.Task, objDTGrid.Rows(i).Item("Col_1"), tasksevents.EventTypes.EventCase, 0, 0, GlobalCRM.CurrentConID, Format(Now, "dd-MMM-yyyy"), 1)
                                    End If
                                End If
                            End If
                        End If
                    Next
                End If
            End If
            Me.grdCases.ItemsSource = objDTGrid
            Me.GetColumns()

            ' set global variable for current consultant you are viewing info for
            GlobalCRM.CurrentConViewID = Me.drpCons.SelectedValue

            'Me.Cursor = Cursors.Default
        Catch ex As Exception
            CommonFuncs.ErrorHandler(ex, "frmMainForm.drpCons.SelectedIndexChanged", False)
        End Try
    End Sub

    Public Sub GetColumns()

        Dim intTitle As Integer
        Dim strField As String

        If Not objDTGrid Is Nothing Then
            'krishan name the column header of grdcases on teh name of the data returned in table
            ReDim StrFieldNames(objDTGrid.Columns.Count)
            For count As Int32 = 0 To objDTGrid.Columns.Count - 1
                StrFieldNames(count) = objDTGrid.Columns(count).ColumnName
            Next

        Else
            StrFieldNames(0) = "Case ID"
            StrFieldNames(1) = "Company "
            StrFieldNames(2) = "Cand Phone "
            StrFieldNames(3) = "Contact"
            StrFieldNames(4) = "Product"
            StrFieldNames(5) = "Candidate"
            StrFieldNames(6) = "Status"
            StrFieldNames(7) = "Product Type"
            StrFieldNames(8) = "Date Sold"
            Me.grdCases.Visibility = System.Windows.Visibility.Collapsed
            Exit Sub
        End If




        Try
            With Me.grdCases
                '.Columns(0).Header = "Case ID"
                For intTitle = 0 To objDTGrid.Columns.Count - 1
                    strField = .Columns(intTitle).Header
                    .Columns(intTitle).Header = StrFieldNames(intTitle)
                Next
            End With
        Catch ex As Exception
            CommonFuncs.ErrorHandler(ex, "frmMainForm.GetColumns", False)
        End Try

    End Sub

    Private Sub drpTaskView_SelectionChanged(ByVal sender As Object, ByVal e As System.Windows.Controls.SelectionChangedEventArgs)
        Try
            Me.RefreshLists()
        Catch ex As Exception
            CommonFuncs.ErrorHandler(ex, "frmCRMMainForm.drpTaskView_SelectionChanged", False)
        End Try
    End Sub

    Private Sub RefreshLists()
        Try

            Dim itemTable As System.Data.DataTable
            Dim taskDate As Date = Me.TaskViewEnd()
            itemTable = CommonDataFunctions.GetSQLDatatable(EventList(Me.drpCons.SelectedValue, taskDate))
            DataBinding.BindListView(itemTable, Me.lvwEventsOutstanding)
            Dim sql As String = Me.CaseListSQL(Me.StatusCaseStatus, Me.StatusConRole, OnHold.Exclude)
            objDTGrid = CommonDataFunctions.GetSQLDatatable(sql)
            'Global_Visas_Business_Layer.DataBinding(objDTGrid, grdCases)
            Me.grdCases.ItemsSource = objDTGrid

            Me.GetColumns()

            Me.txtMessage.Text = String.Empty
            Me.btnDismissTask.IsEnabled = False
        Catch ex As Exception
            CommonFuncs.ErrorHandler(ex, "frmMainForm.RefreshLists", False)
        End Try

    End Sub

#Region "SQL"

    Private Function EventList(ByVal ConId As Integer, ByVal ViewTo As DateTime) As String

        Dim SQL As New StringBuilder

        With SQL

            .Append("SELECT D_Event.Event_ID, Case Event_VIP When 0 then D_Event.Event_Description When 1 then 'URGENT: ' +D_Event.Event_Description end as Description, Convert(varchar(10), Event_DTScheduled, 3) as SchDT")

            'Case select to pull out context sensitive event linking (i.e. to company, person or case dependant on the linktype value)
            .Append(" , isnull(CASE event_linktype ")
            .Append(" WHEN 1 THEN 'Case ' + cast(D_event.event_linkID AS varchar) + ' (' + product_name + ' for ' + isnull(d_company_case.company_name,'') + ' ' +  isnull(D_Person_Candidate.Person_Fname + ' ' + D_Person_Candidate.Person_Lname,'No Candidate') + ')' ")
            .Append("WHEN 2 THEN D_Person.Person_FName + ' ' + D_Person.Person_LName + isnull(d_company_person.company_name,'')")
            .Append(" WHEN 3 THEN D_Company.Company_Name ")
            .Append(" WHEN 4 THEN 'User Task' ")
            .Append(" ELSE 'Unlinked' END ,'Link not found') ")
            .Append(" AS EventLink, Replace(Replace(Replace(Cast(Event_Message as VarChar(50)), CHAR(10), '  '), CHAR(13), '  '), CHAR(9), '  ') as Message, Event_LinkID, Event_LinkType")  ' replacing new paragraph marks with spaces
            '.Append(" AS EventLink, Cast(Event_Message as VarChar(50)) as Message, Event_LinkID, Event_LinkType")

            'From clauses
            .Append(" FROM D_Person D_Person_Candidate RIGHT OUTER JOIN")
            .Append(" D_Company D_Company_Case RIGHT OUTER JOIN")
            .Append(" D_Case ON D_Company_Case.Company_ID = D_Case.Case_CompanyID ON")
            .Append(" D_Person_Candidate.Person_ID = D_Case.Case_CandidateID LEFT OUTER JOIN")
            .Append(" L_Products ON D_Case.Case_ProductID = L_Products.Product_ID RIGHT OUTER JOIN")
            .Append(" D_Event LEFT OUTER JOIN")
            .Append(" D_Person LEFT OUTER JOIN")
            .Append(" D_Company D_Company_Person ON D_Person.Person_CompanyID = D_Company_Person.Company_ID ON")
            .Append(" D_Event.Event_LinkID = D_Person.Person_ID ON D_Case.Case_ID = D_Event.Event_LinkID LEFT OUTER JOIN")
            .Append(" D_Company ON D_Event.Event_LinkID = D_Company.Company_ID")

            'VB CODE DEFINITION Where clause uses consultant ID and date to view from
            .Append(" WHERE (D_Event.Event_Category = 2) ")
            .Append(" AND (D_Event.Event_ScheduledFor = " & ConId & ") ")
            .Append(" AND (D_Event.Event_DTScheduled < CONVERT(DATETIME,'" & CStr(Format(ViewTo, "dd-MMM-yyyy")) & " 23:59:59', 102))")

            'Order By Clause
            .Append(" ORDER BY Event_VIP Desc, Event_DTScheduled")

        End With

        Return SQL.ToString


    End Function

    Private Function CaseListSQL(ByVal ViewStatus As CaseStatus, ByVal Role As ConRole, ByVal Hold As OnHold) As String
        'krishan -commented due to some reason
        'Return (Me.SQL_Caselist())
        'Exit Function

        Dim SQL As New StringBuilder

        With SQL
            .Append("Select D_Case.Case_ID as [Case ID], D_Company.Company_Name as Company, Case D_Case.Case_CompanyId when 0 then '' else isnull(D_Person_Contact.Person_Fname + ' ' + D_person_contact.Person_Lname,'Unspecified') end as Contact,")
            .Append("L_Products.Product_Name as Product, isnull(D_Person_candidate.Person_fname + ' ' + D_Person_candidate.Person_lname,'Unspecified') as Candidate,")
            .Append("isnull(L_CaseStages.CaseStage_Title,'Unspecified') as Status, CONVERT (varchar(12), D_Case.Case_DTStatus, 103) as [Date Updated],")
            .Append("isnull(L_ProductTypes.ProductType_Name,'') as [Product Type], CONVERT (varchar(12), D_Case.Case_DTSold,103) as [Date Sold] ")
            .Append("From D_Case Left Outer Join D_Company on D_Case.Case_CompanyID = D_Company.Company_ID Left Outer Join D_Person D_Person_Candidate on D_Case.Case_CandidateID = D_Person_Candidate.Person_ID Left Outer Join D_Person D_Person_Contact on D_Case.Case_ContactID = D_Person_Contact.Person_ID ")
            .Append("Left Outer Join L_products on D_Case.Case_ProductID = L_Products.Product_ID ")
            .Append("Left Outer Join L_CaseStages on D_Case.Case_StatusID = L_CaseStages.CaseStage_Number ")
            .Append("Left Outer Join L_Products L_Products_TypeLink on D_Case.Case_ProductID = L_Products_TypeLink.Product_ID Left Outer Join L_ProductTypes on L_Products_TypeLink.Product_Type = L_ProductTypes.ProductType_ID ")
            .Append("WHERE ")

            Dim statusOfAnd As Boolean = False
            Select Case ViewStatus
                Case CaseStatus.All
                    .Append("(D_Case.Case_StatusID < 1000) ")
                    .Append("AND ")

                Case CaseStatus.PreSale
                    .Append("(D_Case.Case_StatusID < 100) ")
                    .Append("AND ")

                Case CaseStatus.Initiated
                    .Append("(D_Case.Case_StatusID between 100 and 999) ")
                    .Append("AND ")

                Case Else
                    statusOfAnd = False
                    Exit Select

            End Select

            '.Append("AND D_Case.Case_ActiveStatus = 0 AND ")


            Select Case Role
                Case ConRole.SalesCon
                    .Append("(D_Case.Case_ConSaleID = " & Me.drpCons.SelectedValue & ") ")
                    .Append("AND ")
                Case ConRole.CaseWorker
                    .Append("(D_Case.Case_ConCWID = " & Me.drpCons.SelectedValue & ") ")
                    .Append("AND ")
                Case ConRole.Either
                    .Append("(D_Case.Case_ConSaleID = " & Me.drpCons.SelectedValue & " or D_Case.Case_ConCWID = " & Me.drpCons.SelectedValue & " or D_CASE.CASE_COORDINATORID = " & Me.drpCons.SelectedValue & ")")
                    .Append("AND ")
                Case ConRole.CaseCoordinator
                    .Append("(D_CASE.CASE_COORDINATORID = " & Me.drpCons.SelectedValue & ")")
                    .Append("AND ")
                Case Else
                    statusOfAnd = False
                    Exit Select
            End Select


            Select Case Hold
                Case OnHold.Exclude
                    If statusOfAnd = True Then
                        .Append(" AND D_Case.Case_ActiveStatus = 0")
                    Else
                        .Append(" D_Case.Case_ActiveStatus = 0")
                    End If
                Case OnHold.Include
                    'No restriction
                Case OnHold.Only
                    If statusOfAnd = True Then
                        .Append(" AND D_Case.Case_ActiveStatus = 1")
                    Else
                        .Append(" D_Case.Case_ActiveStatus = 1")
                    End If
            End Select
            ' krishan - added
            .Append(" and d_case.Case_ConSaleID=(select con_id from l_consultant where con_NTUsername='" + Environment.UserName() + "')")

            .Append(" Order By D_Case.Case_ID") ' DESC")

        End With

        Return SQL.ToString

    End Function


#End Region

    Public Function SQL_Caselist() As String

        Dim StrModule As String, IntErr As Integer, Ret As Object

        Dim objDR As DataRow
        StrModule = "Form_Fm_MainMenu.SQL_CaseList"

        Dim Gbl_Int_ConID As Integer = CommonDataFunctions.GetLoggedOnConsultantID

        Dim intFormLayout As Integer = Convert.ToInt32(drpView.Text.ToString)

        Try

            Dim StrSQL As String = "", objDT As DataTable, IntMenu As Integer, _
                    IntColumn As Integer, ObjBtn As Object = Nothing

            Dim StrSQLSelect As String = "", StrSQLFrom As String = "", _
                StrSQLWhere As String = "", StrSQLOrder As String = ""

            'First get the menu layout for this user.

            StrSQL = "Select Con_MenuLayout1, Con_MenuLayout2, Con_MenuLayout3 from L_Consultant where Con_ID = " & Gbl_Int_ConID
            objDT = CommonDataFunctions.GetSQLDatatable(StrSQL)

            If objDT.Rows.Count = 0 Then
                'Use system default (1)
                IntMenu = 1
            Else
                objDR = objDT.Rows(0)
                Select Case intFormLayout
                    Case 1
                        IntMenu = objDR("Con_MenuLayout1")

                    Case 2
                        IntMenu = objDR("Con_MenuLayout2")
                        If IntMenu = 0 Then
                            Call MsgBox("No second menu defined, changing to default")
                            'Me.frm_MenuLayout = 1
                            IntMenu = 1
                        End If

                    Case 3
                        IntMenu = objDR("Con_MenuLayout3")
                        If IntMenu = 0 Then
                            Call MsgBox("No third menu defined, changing to default")
                            'Me.frm_MenuLayout = 1
                            IntMenu = 1
                        End If

                    Case Else
                        IntMenu = objDR("Con_MenuLayout1")

                End Select

            End If

            'Build the basic From clause by suming the basic table requirements

            StrSQL = "SELECT SUM(I_MenuOption.MenuOption_TCompany) AS Comp, SUM(I_MenuOption.MenuOption_TPersonCandidate) AS Cand, "
            StrSQL = StrSQL & " SUM(I_MenuOption.MenuOption_TPersonContact) AS Cont, SUM(I_MenuOption.MenuOption_TPersonPartner) AS Part"
            StrSQL = StrSQL & " , SUM(I_MenuOption.MenuOption_TEvent) AS Event"
            StrSQL = StrSQL & " , SUM(I_MenuOption.MenuOption_TCaseFinance) AS CaseFinance"
            StrSQL = StrSQL & " FROM D_MenuLink LEFT OUTER JOIN"
            StrSQL = StrSQL & " I_MenuOption ON D_MenuLink.MenuLink_ItemID = I_MenuOption.MenuOption_ID"
            StrSQL = StrSQL & " WHERE (D_MenuLink.MenuLink_LayoutID = " & IntMenu & ")"
            StrSQL = StrSQL & " GROUP BY D_MenuLink.MenuLink_LayoutID"

            objDT = CommonDataFunctions.GetSQLDatatable(StrSQL)

            If objDT.Rows.Count = 0 Then
                Call MsgBox("Problem reading Menu Layout, please speak to IT", , "Error")
                Me.grdCases.ItemsSource = Nothing
                Me.grdCases.Rebind()
                Return ""
                Exit Function
            End If

            objDR = objDT.Rows(0)

            StrSQLFrom = "From D_Case"

            If objDR("comp") > 0 Then
                StrSQLFrom = StrSQLFrom & " Left Outer Join D_Company on D_Case.Case_CompanyID = D_Company.Company_ID"
            End If

            If objDR("part") > 0 Then
                'Adds in Candidate automaticaly
                StrSQLFrom = StrSQLFrom & " LEFT OUTER JOIN"
                StrSQLFrom = StrSQLFrom & " D_Person D_Person_Candidate ON D_Case.Case_CandidateID = D_Person_Candidate.Person_ID LEFT OUTER JOIN"
                StrSQLFrom = StrSQLFrom & " D_Dependants LEFT OUTER JOIN"
                StrSQLFrom = StrSQLFrom & " D_Person D_Person_Partner ON D_Dependants.Dependant_ChildID = D_Person_Partner.Person_ID ON"
                StrSQLFrom = StrSQLFrom & " D_Person_Candidate.Person_ID = D_Dependants.Dependant_ParentID"
            Else
                If objDR("cand") > 0 Then
                    StrSQLFrom = StrSQLFrom & " Left Outer Join D_Person D_Person_Candidate on D_Case.Case_CandidateID = D_Person_Candidate.Person_ID"
                End If
            End If


            If objDR("cont") > 0 Then
                StrSQLFrom = StrSQLFrom & " Left Outer Join D_Person D_Person_Contact on D_Case.Case_ContactID = D_Person_Contact.Person_ID"
            End If

            If objDR("event") > 0 Then
                StrSQLFrom = StrSQLFrom & " Left Outer Join ("

                StrSQLFrom = StrSQLFrom & " SELECT     "
                StrSQLFrom = StrSQLFrom & " Event_LinkID,"
                StrSQLFrom = StrSQLFrom & " convert(varchar(12),max(Event_DTEntered),103) as LastEvent,"
                StrSQLFrom = StrSQLFrom & " convert(varchar(12),min(case Event_Category when 2 then Event_DTScheduled else null end),103) as NextTask,"
                StrSQLFrom = StrSQLFrom & " convert(varchar(12),max(case Event_Category when 3 then Event_DTCompleted else null end),103) as LastComp,"
                StrSQLFrom = StrSQLFrom & " count(Event_ID) as TotalEvents,"
                StrSQLFrom = StrSQLFrom & " sum(Case Event_Category when 2 then 1 when 3 then 1 else 0 end) as TotalTasks,"
                StrSQLFrom = StrSQLFrom & " Sum(Case Event_Category when 2 then 1 else 0 end) as TotalSched,"
                StrSQLFrom = StrSQLFrom & " Sum(Case Event_Category when 3 then 1 else 0 end) as TotalComp,"
                StrSQLFrom = StrSQLFrom & " sum( Case When Event_Category = 2 AND Event_DTScheduled < getdate() then 1 else 0 end) as TotalOS"

                StrSQLFrom = StrSQLFrom & " FROM D_Event"
                StrSQLFrom = StrSQLFrom & " WHERE (Event_LinkType = 1)"
                StrSQLFrom = StrSQLFrom & " Group By Event_LinkID"

                StrSQLFrom = StrSQLFrom & ") D_Event_Case on D_Case.Case_ID = D_Event_Case.Event_LinkID"
            End If

            If objDR("CaseFinance") > 0 Then
                StrSQLFrom = StrSQLFrom & " Left Outer Join V_Case on D_Case.Case_ID = V_Case.Case_ID"
            End If



            'Get all the layout items to build the select clause, the rest of the from clause and label the columns

            StrSQL = "SELECT D_MenuLink.MenuLink_Column, I_MenuOption.MenuOption_ColumnTitle"
            StrSQL = StrSQL & " ,I_MenuOption.MenuOption_SQLSelect , I_MenuOption.MenuOption_SQLFrom"
            StrSQL = StrSQL & " FROM D_MenuLink LEFT OUTER JOIN"
            StrSQL = StrSQL & " I_MenuOption ON D_MenuLink.MenuLink_ItemID = I_MenuOption.MenuOption_ID"
            StrSQL = StrSQL & " WHERE (D_MenuLink.MenuLink_LayoutID = " & IntMenu & ")"
            StrSQL = StrSQL & " ORDER BY D_MenuLink.MenuLink_Column"

            objDT = CommonDataFunctions.GetSQLDatatable(StrSQL)

            If objDT.Rows.Count > 0 Then
                If objDT.Rows.Count < 8 Then
                    Call MsgBox("Incomplete Menu Layout, please speak to IT", , "Error")
                    Me.grdCases.ItemsSource = Nothing
                    Me.grdCases.Rebind()
                    Return ""
                    Exit Function
                End If
            Else
                Call MsgBox("Empty Menu Layout, please speak to IT", , "Error")
                Me.grdCases.ItemsSource = Nothing
                Me.grdCases.Rebind()
                Return ""
                Exit Function
            End If

            'Build select clause
            StrSQLSelect = "Select D_Case.Case_ID as Col_1"

            ReDim StrFieldNames(0 To objDT.Rows.Count + 1)
            Dim intPtr As Integer = 1

            For Each objDR In objDT.Rows
                StrSQLSelect = StrSQLSelect & ", " & IIf(objDR("MenuOption_SQLSelect") Is DBNull.Value, True, objDR("MenuOption_SQLSelect")) & " as Col_" & objDR("MenuLink_Column")
                StrSQLFrom = StrSQLFrom & " " & objDR("Menuoption_sqlfrom")
                StrFieldNames(intPtr) = objDR("Menuoption_ColumnTitle")
                intPtr += 1
            Next


            If radViewPreSale.IsChecked = True Then
                'Pre Sale cases
                StrSQLWhere = "WHERE  (D_Case.Case_StatusID < 100)"
            ElseIf radViewInitiated.IsChecked = True Then
                'Initiated Cases
                StrSQLWhere = "WHERE (D_Case.Case_StatusID BETWEEN 100 AND 999) "
            ElseIf radViewAll.IsChecked = True Then
                'All cases
                StrSQLWhere = "WHERE (D_Case.Case_StatusID < 1000)"
            End If

            'If Not Me.Chk_OnHold Then
            '   StrSQLWhere = StrSQLWhere & " AND D_Case.Case_ActiveStatus = 0"
            'End IF

            If radHoldExclude.IsChecked = True Then
                'Exclude
                StrSQLWhere = StrSQLWhere & " AND D_Case.Case_ActiveStatus = 0"
            ElseIf radHoldInclude.IsChecked = True Then
                'Include
                'No restriction
            ElseIf radHoldOnly.IsChecked = True Then
                'Show Only
                StrSQLWhere = StrSQLWhere & " AND D_Case.Case_ActiveStatus = 1"
            End If

            If radRoleSales.IsChecked = True Then
                'Sales Con
                StrSQLWhere = StrSQLWhere & " AND (D_Case.Case_ConSaleID = " & Me.drpCons.SelectedValue & ")"
            ElseIf radRoleCW.IsChecked = True Then
                'Caseworker
                StrSQLWhere = StrSQLWhere & " AND (D_Case.Case_ConCWID = " & Me.drpCons.SelectedValue & ")"
            ElseIf radRoleCaseCoordinator.IsChecked = True Then
                ' Case Coordinator
                StrSQLWhere = StrSQLWhere & " AND (D_Case.Case_CoordinatorID = " & Me.drpCons.SelectedValue & ")"
            ElseIf radRoleEither.IsChecked = True Then
                'Either
                StrSQLWhere = StrSQLWhere & " AND ((D_Case.Case_ConSaleID = " & Me.drpCons.SelectedValue & ") OR (D_Case.Case_ConCWID = " & Me.drpCons.SelectedValue & ") or D_Case.Case_CoordinatorID = " & Me.drpCons.SelectedValue & ")"
            End If

            'Work out Order By caluse

            'If Me.Frm_OrderBy = 1 Then
            ' 'By Case ID
            ' StrSQLOrder = " Order By D_Case.Case_ID"
            ' If Me.Frm_OrderDirection = 2 Then StrSQLOrder = StrSQLOrder & " DESC"
            ' Else
            'By custom column, go get the SQL


            'StrSQL = "SELECT I_MenuOption.MenuOption_SQLOrderBy1, I_MenuOption.MenuOption_SQLOrderBy2"
            'StrSQL = StrSQL & " FROM I_MenuOption INNER JOIN"
            'StrSQL = StrSQL & " D_MenuLink ON I_MenuOption.MenuOption_ID = D_MenuLink.MenuLink_ItemID"
            'StrSQL = StrSQL & " WHERE (D_MenuLink.MenuLink_LayoutID = " & IntMenu & ") And (D_MenuLink.MenuLink_Column = " & Me.Frm_OrderBy & ")"

            'Rst = GSS(StrSQL)

            'If Rst.RecordCount = 0 Then
            ' 'Can't find it, use Case ID
            'StrSQLOrder = " Order By D_Case.Case_ID"
            'If Me.Frm_OrderDirection = 2 Then StrSQLOrder = StrSQLOrder & " DESC"
            'Else
            'StrSQLOrder = " Order By " & Rst.Fields("Menuoption_SQLOrderBy1")
            'If Me.Frm_OrderDirection = 2 Then StrSQLOrder = StrSQLOrder & " DESC"

            '        If IIf(objDR("MenuOption_SQLOrderBy2") Is DBNull.Value, "", objDR("Men'uOption_SQLOrderBy2")) <> "" Then
            'StrSQLOrder = StrSQLOrder & ", " & Rst.Fields("Menuoption_SQLOrderBy2")
            'If Me.Frm_OrderDirection = 2 Then StrSQLOrder = StrSQLOrder & " DESC"
            '        Else
            'StrSQLOrder = StrSQLOrder & ", D_case.Case_ID "
            'If Me.Frm_OrderDirection = 2 Then StrSQLOrder = StrSQLOrder & " DESC"
            'End If

            '       End If
            '      Rst.Close()
            '     End If

            StrSQL = StrSQLSelect & " " & StrSQLFrom & " " & StrSQLWhere & " " & StrSQLOrder

            Return (StrSQL)


            '    Call SQL_PipelineDetails()

            ' Error handling block added by VBA Code Commenter and Error Handler Add-In. DO NOT EDIT this block of code.
            Exit Function

        Catch ex As Exception
            CommonFuncs.ErrorHandler(ex, "frmMainForm.SQL_CASELIST", False)
            Ret = MsgBox("An error has occured, please contact the IT dept and quote the following code: " & IntErr, , "Error")
        Finally
        End Try
        ' End Error handling block.

    End Function

    Private Sub drpBranch_SelectionChanged(ByVal sender As Object, ByVal e As System.Windows.Controls.SelectionChangedEventArgs)

        Try
            With Me.drpDepts
                .SelectedValuePath = "department_id"
                .SelectedValuePath = "department_name"
                .ItemsSource = CType(CommonDataFunctions.DepartmentList(Me.drpBranch.SelectedValue), IListSource).GetList
                .SelectedIndex = -1
                drpDepts_SelectionChanged(sender, e)
            End With
        Catch ex As Exception
            CommonFuncs.ErrorHandler(ex, "frmMainForm.drpBranch_SelectedIndexChanged", False)
        End Try
    End Sub

    Private Sub btnAllocation_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles btnAllocation.Click
        Try
            'MainDisplay.Focus()
            'GlobalCRM.CurrentBranchId = CommonFunc.GetBranchID(GlobalCRM.CurrentConID)
            'frmUnassignedCases.MdiParent = frmMDIMain
            Dim UnassignedCasesForm As New frmUnassignedCases()
            UnassignedCasesForm.ShowDialog()
        Catch ex As Exception
            CommonFuncs.ErrorHandler(ex, "frmMainForm.btnAllocation_Click", False)
        End Try
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs)
        'Dim f1 As New frmAdminEmailTemplates
        Dim f1 As New frmAdminCustomizeCRMView
        f1.ShowDialog()

    End Sub
   
   
    Private Sub grdCases_MouseDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Input.MouseButtonEventArgs) Handles grdCases.MouseDoubleClick
        Dim group1 As Telerik.Windows.Controls.GridView.GridViewGroupRow

        Try
            group1 = New Telerik.Windows.Controls.GridView.GridViewGroupRow

            If grdCases.CurrentItem.ToString = group1.ToString Then
                MsgBox("Please Double click on Item level. Double clicking on Group Item is not valid", MsgBoxStyle.OkOnly, "Error")
                group1 = Nothing
                Exit Sub
            End If

            Dim currentRow As DataRow = grdCases.CurrentItem

            GlobalCRM.CurrentCaseID = currentRow(0)

            ' log event and userhistory records 
            CommonFuncs.GblFunc_Goto_ID(GlobalCRM.CurrentCaseID, CommonFuncs.DBIdTypes.CaseID)

            'frmMainDisplay.MdiParent = frmMDIMain
            Dim MainFormDisplay As New frmMainDisplay()
            MainFormDisplay.ShowDialog()
        Catch ex As Exception
            CommonFuncs.ErrorHandler(ex, "frmMainForm.grdCases_MouseDoubleClick", False)
        Finally
            group1 = Nothing
        End Try
    End Sub

    Private Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles btnSearch.Click
        Try
            Dim SearchForm As New frmSearch()
            SearchForm.ShowDialog()
        Catch ex As Exception
            CommonFuncs.ErrorHandler(ex, "frmMainForm.btnSearch_Click", False)
        End Try
    End Sub

    Private Sub btnRefresh_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles btnRefresh.Click
        Try
            Me.RefreshLists()
        Catch ex As Exception
            CommonFuncs.ErrorHandler(ex, "frmMainForm.btnRefresh_Click", False)
        End Try

    End Sub

  
End Class


